
vim /root/dwd_inc_dim_aml_account.sql
INSERT INTO TABLE DWD.DIM_AML_ACCOUNT PARTITION (ETL_DATE='${hivevar:etl_dt}')
SELECT ACCOUNT_ID,
       CUSTOMER_ID,
       ACCT_NO,
       CASE WHEN ACCT_TYPE = "SAVING" THEN "储蓄"
            WHEN ACCT_TYPE = "INVESTMENT" THEN "投资"
            WHEN ACCT_TYPE = "CURRENT" THEN "活期"
            WHEN ACCT_TYPE = "CREDIT" THEN "信用卡"
            WHEN ACCT_TYPE = "LOAN" THEN "贷款"
            ELSE ACCT_TYPE
           END AS ACCT_TYPE,
       CURRENCY,
       CURRENT_BALANCE,
       AVG_DAILY_BALANCE,
       CASE WHEN STATUS = "ACTIVE" THEN "活跃"
            WHEN STATUS = "INACTIVE" THEN "休眠"
            WHEN STATUS = "CLOSED" THEN "已销户"
            WHEN STATUS = "FROZEN" THEN "冻结"
            ELSE STATUS
           END AS STATUS,
       BRANCH_CODE,
       CASE WHEN CHANNEL_OPEN = "COUNTER" THEN "柜面"
            WHEN CHANNEL_OPEN = "ONLINE" THEN "网银"
            WHEN CHANNEL_OPEN = "MOBILE" THEN "手机银行"
            ELSE CHANNEL_OPEN
           END AS CHANNEL_OPEN,
       OPEN_DATE,
       '9999-12-31'
FROM ODS.ODS_AML_ACCOUNT_MASTER
WHERE etl_date ="${hivevary:etl_date}";

vim /root/dwd_inc_dim_aml_customer.sql
INSERT INTO TABLE DWD.DIM_AML_CUSTOMER PARTITION (ETL_DATE='${hivevar:etl_dt}')
SELECT
    M.CUSTOMER_ID
     ,M.CUST_NO
     ,M.CUST_NAME
     ,CASE WHEN M.CUST_TYPE = 'IND' THEN '个人'
           WHEN M.CUST_TYPE = 'CORP' THEN '对公'
           ELSE M.CUST_TYPE
    END AS CUST_TYPE
     ,CASE WHEN M.ID_TYPE = 'ID_CARD'  THEN '身份证'
           WHEN M.ID_TYPE = 'PASSPORT' THEN '护照'
           WHEN M.ID_TYPE = 'BUS_LIC'  THEN '营业执照'
           ELSE M.ID_TYPE
    END AS ID_TYPE
     ,CASE
          WHEN M.RISK_LEVEL = 'LOW' THEN '低'
          WHEN M.RISK_LEVEL = 'MEDIUM' THEN '中'
          WHEN M.RISK_LEVEL = 'HIGH' THEN '高'
          WHEN M.RISK_LEVEL = 'VERY_HIGH' THEN '极高'
          ELSE M.RISK_LEVEL
    END AS RISK_LEVEL
     ,CASE
          WHEN M.RISK_LEVEL_SOURCE = 'MANUAL' THEN '人工'
          WHEN M.RISK_LEVEL_SOURCE = 'AUTO' THEN '系统自动'
          ELSE M.RISK_LEVEL_SOURCE
    END AS RISK_LEVEL_SOURCE
     ,CASE
          WHEN M.STATUS = 'ACTIVE'  THEN '活跃'
          WHEN M.STATUS = 'INACTIVE'  THEN '休眠'
          WHEN M.STATUS = 'CLOSED'  THEN '已销户'
          WHEN M.STATUS = 'FROZEN'  THEN '冻结'
          ELSE M.STATUS
    END AS  STATUS
     ,M.INDUSTRY
     ,M.OCCUPATION
     ,M.NATIONALITY
     ,M.RESIDENCE_CITY
     ,M.OPEN_DATE AS START_DATE
     ,'9999-12-31' AS END_DATE
     ,TRUE AS IS_CURRENT
FROM ODS.ODS_AML_CUSTOMER_MASTER M
WHERE CLOSE_DATE IS NULL;

vim /root/dwd_inc_dim_aml_rule.sql
INSERT OVERWRITE TABLE DWD.DIM_AML_RULE PARTITION (ETL_DATE='${hivevar:etl_dt}')
SELECT
    RULE_ID,
    RULE_NAME,
    CASE WHEN RULE_TYPE="BEHAVIOR" THEN "行为"
         WHEN RULE_TYPE="PATTERN"  THEN "模式"
         WHEN RULE_TYPE="FREQUENCY" THEN "频率"
         WHEN RULE_TYPE="AMOUNT" THEN "金额"
         ELSE  RULE_TYPE
        END   AS   RULE_TYPE,
    CASE WHEN RULE_CATEGORY="CASH" THEN "现金"
         WHEN RULE_CATEGORY="TFR" THEN "转账"
         WHEN RULE_CATEGORY="CROSS_BORDER" THEN "跨境"
         WHEN RULE_CATEGORY="GAMBLING" THEN "赌博"
         ELSE   RULE_CATEGORY
        END      AS RULE_CATEGORY,
    CASE WHEN SEVERITY_LEVEL="MEDIUM" THEN "中"
         WHEN SEVERITY_LEVEL="LOW" THEN "低"
         WHEN SEVERITY_LEVEL="HIGH" THEN "高"
         WHEN SEVERITY_LEVEL="CRITICAL" THEN "严重"
         ELSE   SEVERITY_LEVEL
        END AS SEVERITY_LEVEL,
    CASE WHEN ACTIVE="Y" THEN "是"
         WHEN ACTIVE="N" THEN "否"
         ELSE ACTIVE
        END AS ACTIVE_STATUS,
    COALESCE(GET_JSON_OBJECT(PARAM_JSON,"$.THRESHOLD"),"无") AS THRESHOLD
FROM ODS.ODS_AML_MONITORING_RULE;

vim /root/dwd_inc_dim_aml_watchlist.sql
INSERT INTO TABLE DWD.DIM_AML_WATCHLIST PARTITION (ETL_DATE='${hivevar:etl_dt}')
SELECT
    B.ENTITY_ID
     ,A.LIST_CODE
     ,CASE
          WHEN A.LIST_TYPE = 'TERRORIST' THEN '恐怖'
          WHEN A.LIST_TYPE = 'SANCTIONS' THEN '制裁'
          WHEN A.LIST_TYPE = 'CRIMINAL' THEN '犯罪'
          WHEN A.LIST_TYPE = 'PEP' THEN '政要'
          WHEN A.LIST_TYPE = 'OTHER' THEN '其他'
    END AS LIST_TYPE
     ,COALESCE(CASE
                   WHEN A.LIST_SOURCE = 'UN' THEN '联合国'
                   WHEN A.LIST_SOURCE = 'OFAC' THEN '美国财政部'
                   WHEN A.LIST_SOURCE = 'MPS' THEN '中国公安部'
                   WHEN A.LIST_SOURCE = 'INTERNAL' THEN '内部名单'
                   ELSE A.LIST_SOURCE
                   END , '无')AS LIST_SOURCE
     ,CASE
          WHEN B.ENTITY_TYPE = 'PERSON' THEN '个人'
          WHEN B.ENTITY_TYPE = 'ORG' THEN '组织'
          ELSE B.ENTITY_TYPE
    END AS ENTITY_TYPE
     ,CASE
          WHEN B.SANCTION_TYPE = 'ASSET_FREEZE' THEN '资产冻结'
          WHEN B.SANCTION_TYPE = 'TRAVEL_BAN' THEN '旅行禁令'
          WHEN B.SANCTION_TYPE = 'TRADE_EMBARGO' THEN '贸易禁运'
          WHEN B.SANCTION_TYPE = 'FINANCIAL_SANCTIONS' THEN '金融制裁'
          ELSE B.SANCTION_TYPE
    END AS SANCTION_TYPE
FROM ODS.ODS_AML_WATCHLIST_MASTER A
         JOIN ODS.ODS_AML_WATCHLIST_DETAIL B
              ON A.LIST_ID = B.LIST_ID
WHERE A.ETL_DATE="${hivevary:etl_date}";

vim /root/dwd_inc_fact_aml_alert.sql
INSERT INTO TABLE DWD.FACT_AML_ALERT PARTITION (ETL_DATE='${hivevar:etl_dt}')
SELECT  CUS.CUSTOMER_ID AS CUSTOMER_SK
     ,ACC.ACCOUNT_ID AS ACCOUNT_SK
     ,ALT.RULE_ID AS RULE_SK
     ,CASE WHEN ALT.ALERT_TYPE ='RULE' THEN'规则'
           WHEN ALT.ALERT_TYPE ='MODEL' THEN'模型'
           ELSE ALT.ALERT_TYPE
    END AS ALERT_TYPE
     ,CASE WHEN ALT.ALERT_STATUS ='PENDING' THEN '待处理'
           WHEN ALT.ALERT_STATUS ='REVIEWING' THEN '审核中'
           WHEN ALT.ALERT_STATUS ='DISMISSED' THEN '排除'
           WHEN ALT.ALERT_STATUS ='CONFIRMED' THEN '确认'
           ELSE ALT.ALERT_STATUS
    END AS ALERT_STATUS
     ,CASE WHEN ALT.SEVERITY_LEVEL ='LOW' THEN '低'
           WHEN ALT.SEVERITY_LEVEL ='MEDIUM' THEN '中'
           WHEN ALT.SEVERITY_LEVEL ='HIGH' THEN '高'
           WHEN ALT.SEVERITY_LEVEL ='CRITICAL' THEN '严重'
           ELSE ALT.SEVERITY_LEVEL
    END AS SEVERITY_LEVEL
     ,ALT.TRIGGER_AMOUNT AS TRIGGER_AMOUNT
     ,ALT. TRIGGER_TXN_COUNT AS TRIGGER_TXN_COUNT
FROM ODS.ODS_AML_ALERT ALT
         LEFT JOIN DWD.DIM_AML_ACCOUNT ACC
                   ON ALT.CUSTOMER_ID=ACC.CUSTOMER_ID
         JOIN DWD.DIM_AML_CUSTOMER CUS
              ON CUS.CUSTOMER_ID=ACC.CUSTOMER_ID
WHERE ACC.ETL_DATE ="${hivevary:etl_date}";

vim /root/dwd_inc_fact_aml_screening.sql
insert into table dwd.fact_aml_screening partition (etl_date='${hivevar:etl_dt}')
SELECT
    ASR.RESULT_ID AS screening_sk
     ,ACM.customer_id AS customer_sk
     ,AWD.list_id AS watchlist_sk
     ,CASE
          WHEN ASR.screening_type = 'CUSTOMER' THEN '客户开户'
          WHEN ASR.screening_type = 'TRANSACTION' THEN '交易'
          WHEN ASR.screening_type = 'PERIODIC' THEN '定期回溯'
    END AS screening_type
     ,CASE
          WHEN ASR.TARGET_TYPE = 'CUSTOMER' THEN '客户'
          WHEN ASR.TARGET_TYPE = 'ACCT' THEN '账户'
          WHEN ASR.TARGET_TYPE = 'COUNTERPARTY' THEN '交易对手'
    END AS TARGET_TYPE
     ,CASE
          WHEN ASR.match_level = 'EXACT' THEN '完全匹配'
          WHEN ASR.match_level = 'FUZZY_HIGH' THEN '模糊高'
          WHEN ASR.match_level = 'FUZZY_MED' THEN '模糊中'
          WHEN ASR.match_level = 'FUZZY_LOW' THEN '模糊低'
    END AS MATCH_LEVEL
     ,CASE
          WHEN ASR.screening_status = 'PENDING' THEN '待审'
          WHEN ASR.screening_status = 'ALERT' THEN '警报'
          WHEN ASR.screening_status = 'FALSE_POSITIVE' THEN '误报'
          WHEN ASR.screening_status = 'CONFIRMED' THEN '确认'
    END AS SCREENING_STATUS
     ,ASR.match_score
FROM ODS.ods_aml_watchlist_detail AWD
         JOIN ODS.ods_aml_screening_result ASR
              ON ASR.matched_entity_id = AWD.entity_id
         JOIN ODS.ods_aml_customer_master ACM
              ON ASR.target_id = ACM.customer_id
WHERE ACM.etl_date ="${hivevary:etl_date}";

vim /root/dwd_inc_fact_aml_str_report.sql
INSERT OVERWRITE TABLE DWD.FACT_AML_STR_REPORT PARTITION (ETL_DATE='${hivevar:etl_dt}')
SELECT
    CU.CUSTOMER_ID,
    RE.REPORT_DATE,
    RE.FIRST_TXN_DATE,
    RE.LAST_TXN_DATE,
    CASE WHEN RE.REPORT_TYPE = "INITIAL" THEN "初始"
         WHEN RE.REPORT_TYPE = "AMENDMENT" THEN "修正"
         WHEN RE.REPORT_TYPE = "CLOSURE" THEN "结案"
         ELSE RE.REPORT_TYPE
        END  AS REPORT_TYPE,
    CASE WHEN RE.CASE_CATEGORY = "MONEY_LAUNDERING" THEN "洗钱"
         WHEN RE.CASE_CATEGORY = "TERRORIST_FINANCING" THEN "恐怖融资"
         WHEN RE.CASE_CATEGORY = "FRAUD" THEN "欺诈"
         ELSE RE.CASE_CATEGORY
        END AS CASE_CATEGORY,
    CASE WHEN RE.REPORT_STATUS = "DRAFT" THEN "草稿"
         WHEN RE.REPORT_STATUS = "SUBMITTED" THEN "已提交"
         WHEN RE.REPORT_STATUS = "WITHDRAWN" THEN "已撤回"
         ELSE RE.REPORT_STATUS
        END AS REPORT_STATUS,
    RE.TOTAL_AMOUNT
FROM ODS.ODS_AML_SUSPICIOUS_TXN_REPORT RE
         JOIN DWD.DIM_AML_CUSTOMER CU
              ON RE.CUSTOMER_ID=CU.CUSTOMER_ID;

vim /root/dwd_inc_fact_aml_transaction.sql
INSERT INTO TABLE DWD.FACT_AML_TRANSACTION PARTITION (ETL_DATE='${hivevar:etl_dt}')
SELECT
    CUS.CUSTOMER_ID,
    ACC.ACCOUNT_ID,
    TRA.CURRENCY,
    CASE WHEN TRA.TXN_TYPE='CASH_DEP' THEN "现金存入"
         WHEN TRA.TXN_TYPE='CASH_WD' THEN "现金取款"
         WHEN TRA.TXN_TYPE='TFR_IN' THEN "转入"
         WHEN TRA.TXN_TYPE='TFR_OUT' THEN "转出"
         WHEN TRA.TXN_TYPE='PAYMENT' THEN "支付"
         ELSE TRA.TXN_TYPE
        END AS TXN_TYPE,
    TRA.TXN_SUB_TYPE,
    CASE WHEN TRA.TXN_CHANNEL='COUNTER' THEN "柜面"
         WHEN TRA.TXN_CHANNEL='ATM' THEN "ATM"
         WHEN TRA.TXN_CHANNEL='ONLINE' THEN "网银"
         WHEN TRA.TXN_CHANNEL='MOBILE' THEN "手机银行"
         WHEN TRA.TXN_CHANNEL='POS' THEN "POS"
         ELSE TRA.TXN_CHANNEL
        END AS TXN_CHANNEL,
    CASE WHEN TRA.TXN_STATUS='SUCCESS' THEN "成功"
         WHEN TRA.TXN_STATUS='FAILED' THEN "失败"
         WHEN TRA.TXN_STATUS='PENDING' THEN "待处理"
         ELSE TRA.TXN_STATUS
        END AS TXN_STATUS,
    TRA.TXN_AMOUNT,
    CASE WHEN TRA.REMITTER_COUNTRY="中国" AND TRA.BENEFICIARY_COUNTRY="中国"
             THEN 0
         ELSE 1
        END AS IS_CROSS_BORDER,
    CASE WHEN CUS.RISK_LEVEL='LOW' OR CUS.RISK_LEVEL='MEDIUM'THEN 0
         ELSE 1
        END AS IS_HIGH_RISK
FROM ODS.ODS_AML_CUSTOMER_MASTER CUS
         JOIN ODS.ODS_AML_ACCOUNT_MASTER ACC
              ON CUS.CUSTOMER_ID=ACC.CUSTOMER_ID
         JOIN ODS.ODS_AML_TRANSACTION_DETAIL TRA
              ON ACC.ACCOUNT_ID=TRA.ACCOUNT_ID
WHERE ACC.ETL_DATE="${hivevary:etl_date}";

#!/bin/bash
etl_dt=`date -d '-1 days' +"%Y-%m-%d"`
nohup hive -hivevar etl_dt=$etl_dt -f /root/dwd_inc_dim_aml_customer.sql        >>/aml/dwd/dim_aml_customer_$etl_date.log       &
nohup hive -hivevar etl_dt=$etl_dt -f /root/dwd_inc_dim_aml_account.sql         >>/aml/dwd/dim_aml_account.sql_$etl_date.log    &
nohup hive -hivevar etl_dt=$etl_dt -f /root/dwd_inc_dim_aml_watchlist.sql       >>/aml/dwd/dim_aml_watchlist_$etl_date.log      &
nohup hive -hivevar etl_dt=$etl_dt -f /root/dwd_inc_dim_aml_rule.sql            >>/aml/dwd/dim_aml_rule_$etl_date.log           &
nohup hive -hivevar etl_dt=$etl_dt -f /root/dwd_inc_fact_aml_transaction.sql    >>/aml/dwd/fact_aml_transaction_$etl_date.log   &
nohup hive -hivevar etl_dt=$etl_dt -f /root/dwd_inc_fact_aml_screening.sql      >>/aml/dwd/fact_aml_screening_$etl_date.log     &
nohup hive -hivevar etl_dt=$etl_dt -f /root/dwd_inc_fact_aml_alert.sql          >>/aml/dwd/fact_aml_alert_$etl_date.log         &
nohup hive -hivevar etl_dt=$etl_dt -f /root/dwd_inc_fact_aml_str_report.sql     >>/aml/dwd/fact_aml_str_report_$etl_date.log    &

 #定时抽
 crontab -e
30 1 * * *  bash /root/dsc.sh